﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Configuration;
using log4net;
using TheBullysStore.Models.ModuloAdministracion.Edificio;

namespace TheBullysStore.Models.ModuloAdministracion.AreaComun
{
    public class AreaComunDAO
    {
        public static String cadenaDB = WebConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
        private static ILog log = LogManager.GetLogger(typeof(AreaComunDAO));
        public string RegistrarAreaComun(AreaComunBean areacomun)
        {
            SqlConnection sqlCon = null;
            try
            {
                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();
                SqlCommand sqlCmd;
                string commandString = "INSERT INTO Area_Comun (Id_Edificio, Nombre_Area, Piso, Area, Ubicacion, Es_Reservable, Estado, contometro)  " +
                                       " VALUES ('" + areacomun.IdEdificio + "','" + areacomun.NombreArea + "','" + areacomun.Piso + "'," + areacomun.Area + ",'" + areacomun.Ubicacion + "'," + areacomun.EsReservable + ",1,1)";

               sqlCmd = new SqlCommand(commandString, sqlCon);

                sqlCmd.ExecuteNonQuery();
                
                if (areacomun.EsReservable == 1)
                {
                    commandString = "select max(id_area_comun) from area_comun ";
                                      

                     sqlCmd = new SqlCommand(commandString, sqlCon);

                  areacomun.IdAreaComun=(int)  sqlCmd.ExecuteScalar();

                  commandString = "insert into horario_disponible values ('8-22','8-22','8-22','8-22','8-22','6-24','6-24'," + areacomun.IdAreaComun + ")";


                  sqlCmd = new SqlCommand(commandString, sqlCon);

                  sqlCmd.ExecuteNonQuery();
                }

                

                areacomun.Conexion = "Bien";
            }
            catch (Exception ex)
            {
                log.Error("registrarAreaComun(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }

            return areacomun.Conexion;
        }
        
        public List<AreaComunBean> ListarAreaComun(AreaComunBean areacomun)
        {
            SqlConnection sqlCon = null;
            try
            {
                List<AreaComunBean> listaareacomun = new List<AreaComunBean>();

                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "SELECT * FROM Area_Comun WHERE estado = 1";

                if (areacomun.IdEdificio > 0)
                {
                    commandString += " AND Id_Edificio = " + areacomun.IdEdificio + "";
                }
                if (!string.IsNullOrEmpty(areacomun.NombreArea))
                {
                    commandString += " AND Nombre_Area = '" + areacomun.NombreArea + "'";
                }
                if (!string.IsNullOrEmpty(areacomun.Piso))
                {
                    commandString += " AND Piso = '" + areacomun.Piso + "'";
                }
                if (!string.IsNullOrEmpty(areacomun.Ubicacion))
                {
                    commandString += " AND Ubicacion LIKE '%" + areacomun.Ubicacion + "%'";
                }
                if (areacomun.EsReservable > 0)
                {
                    commandString += " AND Es_Reservable = " + areacomun.EsReservable + "";
                }

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                while (dataReader.Read())
                {
                    AreaComunBean AreaComun = new AreaComunBean();

                    AreaComun.IdAreaComun = Convert.ToInt32(dataReader["Id_Area_Comun"]);
                    AreaComun.IdEdificio = Convert.ToInt32(dataReader["Id_Edificio"]);
                    AreaComun.NombreArea = Convert.ToString(dataReader["Nombre_Area"]);
                    AreaComun.Piso = Convert.ToString(dataReader["Piso"]);
                    AreaComun.Area = Convert.ToDouble(dataReader["Area"]);
                    AreaComun.Ubicacion = Convert.ToString(dataReader["Ubicacion"]);
                    AreaComun.EsReservable = Convert.ToInt32(dataReader["Es_Reservable"]);

                    SqlConnection sqlCon2 = new SqlConnection(cadenaDB);

                    sqlCon2.Open();

                    string commandString2 = "SELECT * FROM Edificio WHERE estado= 1 AND Id_Edificio = " + AreaComun.IdEdificio + "";

                    SqlCommand sqlCmd2 = new SqlCommand(commandString2, sqlCon2);

                    SqlDataReader dataReader2 = sqlCmd2.ExecuteReader();


                    while (dataReader2.Read())
                    {
                        AreaComun.NombreEdificio = Convert.ToString(dataReader2["Nombre"]);

                    }

                    dataReader2.Close();
                    sqlCon2.Close();

                    listaareacomun.Add(AreaComun);
                }

                dataReader.Close();                

                return listaareacomun;

            }
            catch (Exception ex)
            {
                log.Error("listarAreaComun(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
        }

        public string ModificarAreaComun(AreaComunBean areacomun)
        {
            SqlConnection sqlCon = null;
            try
            {

                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET " +
                                       "Id_Edificio = " + areacomun.IdEdificio + ", " +
                                       "Nombre_Area = '" + areacomun.NombreArea + "', " +
                                       "Piso = '" + areacomun.Piso + "', " +
                                       "Area = " + areacomun.Area + ", " +
                                       "Ubicacion = '" + areacomun.Ubicacion + "', " +
                                       "Es_Reservable = " + areacomun.EsReservable + " " +
                                       "WHERE Id_Area_Comun = " + areacomun.IdAreaComun + "";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                sqlCmd.ExecuteNonQuery();

                sqlCon.Close();

                areacomun.Conexion = "Bien";
            }
            catch (Exception ex)
            {
                log.Error("ModificarAreaComun(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
            
            return areacomun.Conexion;
        }

        public string EliminarAreaComun(AreaComunBean areacomun)
        {
            SqlConnection sqlCon = null;
            try
            {
                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Area_Comun = " + areacomun.IdAreaComun + "";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();

                areacomun.Conexion = "Bien";
            }
            catch (Exception ex)
            {
                log.Error("EliminarAreaComun(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
            return areacomun.Conexion;
        }

        public void EliminarAreaComun(EdificioBean edificio)
        {
            SqlConnection sqlCon = null;
            try
            {
                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Edificio = " + edificio.IdEdificio + "";


                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();
                
            }
            catch (Exception ex)
            {
                log.Error("EliminarAreaComun(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
        }

        public void EliminarAreaComunPiso(EdificioBean edificio)
        {
            SqlConnection sqlCon = null;
            try
            {
                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Edificio = " + edificio.IdEdificio +
                " AND SUBSTRING(Piso,2,LEN(Piso)- 1) > " + edificio.NumPisos + " AND SUBSTRING(Piso,1,1) = 'P'";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                log.Error("EliminarAreaComunPiso(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sqlCon.Close();
            }
        }

        public void EliminarAreaComunSotano(EdificioBean edificio)
        {
            SqlConnection sqlCon = null;
            try
            {               
                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "UPDATE Area_Comun SET Estado = 0 WHERE Id_Edificio = " + edificio.IdEdificio +
                                       " AND SUBSTRING(Piso,2,LEN(Piso)- 1) > " + edificio.NumSotanos + " AND SUBSTRING(Piso,1,1) = 'S'";


                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                log.Error("EliminarAreaComunSotano(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
        }

        public List<AreaComunBean> ListarAreaComunCP(AreaComunBean areacomun)
        {
            SqlConnection sqlCon = null;
            try
            {
                List<AreaComunBean> listaAreaComun = new List<AreaComunBean>();

                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "SELECT a.nombre_area, a.id_area_comun " +
                                       "FROM area_comun a " +
                                       "WHERE a.estado = 1 AND a.id_edificio = " + areacomun.IdEdificio;

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);


                SqlDataReader dataReader = sqlCmd.ExecuteReader();



                while (dataReader.Read())
                {
                    AreaComunBean AreaComun = new AreaComunBean();
                    AreaComun.IdAreaComun = Convert.ToInt32(dataReader["id_area_comun"]);
                    AreaComun.NombreArea = Convert.ToString(dataReader["nombre_area"]);
                    listaAreaComun.Add(AreaComun);
                }

                dataReader.Close();                

                return listaAreaComun;

            }
            catch (Exception ex)
            {
                log.Error("ListarAreaComunCP(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
        }
    }
}
